import threading
from typing import Dict, Optional, Any, Tuple
import pymysql
from pymysql.cursors import DictCursor

# 配置MySQL连接参数
db_params = {
    "host": "47.115.213.60",       # 数据库主机地址（默认localhost）
    "port": 3306,              # 端口号（默认3306）
    "user": "root",            # 数据库用户名
    "password": "yuchuang4912",  # 密码（替换为你的实际密码）
    "database": "pyearn",     # 要连接的数据库名（需提前创建）
    "charset": "utf8mb4",      # 字符编码，支持中文
    "cursorclass": DictCursor  # 游标返回字典格式（可选）
}

class MySQLConnectionCache:
    """MySQL数据库连接缓存管理类，使用单例模式确保全局唯一实例"""
    _instance = None
    _lock = threading.Lock()  # 线程安全锁

    def __new__(cls, *args, **kwargs):
        """单例模式实现"""
        if not cls._instance:
            with cls._lock:
                if not cls._instance:
                    cls._instance = super().__new__(cls)
        return cls._instance

    def __init__(self):
        """初始化连接池配置"""
        if not hasattr(self, '_initialized'):  # 防止重复初始化
            self._connection_pools: Dict[str, pymysql.connections.Connection] = {}
            self._pool_configs: Dict[str, dict] = {}  # 存储连接池配置
            self._initialized = True

    def init_pool(self,
                  db_key: str,
                  min_connections: int = 2,
                  max_connections: int = 10, **db_params: Any) -> None:
        """
        初始化数据库连接池

        :param db_key: 连接池唯一标识
        :param min_connections: 最小连接数
        :param max_connections: 最大连接数
        :param db_params: 数据库连接参数，如host, database, user, password等
        """
        if db_key in self._connection_pools:
            return

        # 存储连接池配置
        self._pool_configs[db_key] = {
            "min_connections": min_connections,
            "max_connections": max_connections,
            "db_params": db_params
        }

        # 预创建最小数量的连接
        for _ in range(min_connections):
            conn = self._create_connection(**db_params)
            if conn:
                self._connection_pools[db_key] = self._connection_pools.get(db_key, []) + [conn]

    def _create_connection(self, **db_params: Any) -> Optional[pymysql.connections.Connection]:
        """创建新的数据库连接"""
        try:
            return pymysql.connect(
                **db_params,
                autocommit=False  # 手动控制事务
            )
        except Exception as e:
            print(f"创建数据库连接失败: {str(e)}")
            return None

    def get_connection(self, db_key: str) -> Optional[Tuple[pymysql.connections.Connection, Any]]:
        """
        从缓存中获取数据库连接和游标

        :param db_key: 连接池标识
        :return: 连接对象和游标对象的元组，失败返回None
        """
        if db_key not in self._connection_pools:
            raise ValueError(f"连接池 {db_key} 未初始化")

        try:
            # 从连接池获取连接
            connections = self._connection_pools[db_key]

            if not connections:
                # 如果连接池为空，创建新连接（不超过最大限制）
                config = self._pool_configs[db_key]
                if len(connections) < config["max_connections"]:
                    conn = self._create_connection(**config["db_params"])
                    if not conn:
                        return None
                else:
                    print(f"连接池 {db_key} 已达到最大连接数 {config['max_connections']}")
                    return None
            else:
                conn = connections.pop()  # 取出一个连接

            # 检查连接是否有效
            if conn.ping(reconnect=True):  # 自动重连
                cursor = conn.cursor()
                return conn, cursor
            else:
                # 连接无效，创建新连接
                config = self._pool_configs[db_key]
                new_conn = self._create_connection(**config["db_params"])
                if new_conn:
                    return new_conn, new_conn.cursor()
                return None

        except Exception as e:
            print(f"获取数据库连接失败: {str(e)}")
            return None

    def release_connection(self, db_key: str, conn: pymysql.connections.Connection,
                           cursor: Any, rollback: bool = False) -> None:
        """
        释放连接回连接池

        :param db_key: 连接池标识
        :param conn: 数据库连接对象
        :param cursor: 游标对象
        :param rollback: 是否需要回滚事务
        """
        if db_key not in self._connection_pools:
            return

        try:
            if cursor:
                cursor.close()

            if rollback:
                conn.rollback()

            # 检查连接是否还能使用
            if conn.ping():
                # 确保连接数不超过最大值
                config = self._pool_configs[db_key]
                if len(self._connection_pools[db_key]) < config["max_connections"]:
                    self._connection_pools[db_key].append(conn)
                    return

            # 连接不可用，关闭并创建新连接补充（如果需要）
            conn.close()
            config = self._pool_configs[db_key]
            if len(self._connection_pools[db_key]) < config["min_connections"]:
                new_conn = self._create_connection(**config["db_params"])
                if new_conn:
                    self._connection_pools[db_key].append(new_conn)

        except Exception as e:
            print(f"释放数据库连接失败: {str(e)}")
            try:
                conn.close()
            except:
                pass

    def close_all_connections(self, db_key: Optional[str] = None) -> None:
        """
        关闭指定连接池或所有连接池的连接

        :param db_key: 可选，指定连接池标识，为None时关闭所有
        """
        if db_key:
            if db_key in self._connection_pools:
                for conn in self._connection_pools[db_key]:
                    try:
                        conn.close()
                    except:
                        pass
                del self._connection_pools[db_key]
                del self._pool_configs[db_key]
        else:
            for pool_key in list(self._connection_pools.keys()):
                self.close_all_connections(pool_key)

    def __del__(self):
        """对象销毁时关闭所有连接"""
        self.close_all_connections()

    # 3. 初始化全局唯一的连接缓存实例，并初始化连接池
    # 这一步会在模块加载时执行，确保全局只有一个连接池


global_db_cache = MySQLConnectionCache()
# 用上面的 db_params 初始化连接池（db_key 为 "main_db"）
global_db_cache.init_pool(
    db_key="main_db",
    min_connections=2,
    max_connections=5,
    **db_params
)


#使用示例
if __name__ == "__main__":

    try:
        db_cache = MySQLConnectionCache()

        db_cache.init_pool(
            db_key="main_db",
            min_connections=2,
            max_connections=5,
            **db_params
        )

        # 从缓存获取连接
        conn, cursor = db_cache.get_connection("main_db")
        if conn and cursor:
            # 执行查询
            cursor.execute("SELECT * FROM cb_rate LIMIT 10")
            results = cursor.fetchall()

            # 处理结果（可以按字段名访问）
            for row in results:
                print(f"ID: {row['id']}, Name: {row['name']}")

            # 如果有更新操作，需要手动提交
            # conn.commit()

    except Exception as e:
        print(f"数据库操作出错: {str(e)}")
        rollback = True  # 出错时回滚
    else:
        rollback = False  # 正常执行不回滚
    finally:
        # 释放连接回缓存
        if 'conn' in locals() and 'cursor' in locals():
            db_cache.release_connection("main_db", conn, cursor, rollback=rollback)

    # 程序结束时关闭所有连接（可选）
    # db_cache.close_all_connections("main_db")
